package com.hx.utils;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import java.io.File;
import java.io.FileInputStream;
import java.sql.*;
import java.text.SimpleDateFormat;

/**
 * @Auther: QZ
 * @Date: 2022/6/29 - 06 - 29 - {TIME}
 * @version: 1.0
 */
@Component
public class excelReadEnd {
/*    @Value("${spring.datasource.driver-class-name}")
    public static String driver;
    @Value("${spring.datasource.url}")
    public static String url;
    @Value("${spring.datasource.username}")
    public static String user;
    @Value("${spring.datasource.password}")
    public static String password;*/
    @Autowired
    private Config config;

    private static Connection con = null;
    private static PreparedStatement pstatement=null;
    /**
     * 读取Excel测试，兼容 Excel 2003/2007/2010
     */
    public int readExcel(String tableName,File path)
    {
/*        System.out.println(config.getDriverClassName());
        System.out.println(config.getUrl());
        System.out.println(config.getUsername());*/
        int res = 1;
        //连接数据库
        try {
            Class.forName(config.getDriverClassName());
        } catch (ClassNotFoundException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }
        try {
            con = DriverManager.getConnection(config.getUrl(),config.getUsername(),config.getPassword());
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        if(con!=null) {
            System.out.println("sucess connect to mysql");
        }
        SimpleDateFormat fmt = new SimpleDateFormat("yyyy-MM-dd");
        try {
            //同时支持Excel 2003、2007
            FileInputStream is = new FileInputStream(path); //文件流
            Workbook workbook = WorkbookFactory.create(is); //这种方式 Excel 2003/2007/2010 都是可以处理的
            int sheetCount = workbook.getNumberOfSheets();  //Sheet的数量
            //遍历每个Sheet
            for (int s = 0; s < sheetCount; s++) {
                Sheet sheet = workbook.getSheetAt(s);
                int rowCount = sheet.getPhysicalNumberOfRows(); //获取总行数
                int cellCount=sheet.getRow(0).getPhysicalNumberOfCells();//获取总列数

                String str = "";
                for (int i = 0; i < cellCount; i++) {
                    str += "?,";
                }
                str = StringUtils.chop(str);//[中]从字符串中删除最后一个字符。
                //遍历每一行
                for (int r = 1; r < rowCount; r++) {
                    Row row = sheet.getRow(r);
                    String values[] = new String[cellCount];
                    //遍历每一个单元格
                    for (int c = 0; c < cellCount; c++) {
                        Cell cell = row.getCell(c);
                        int cellType = cell.getCellType();
                        String cellValue = null;
                        //在读取单元格内容前,设置所有单元格中内容都是字符串类型
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        //按照字符串类型读取单元格内数据
                        cellValue = cell.getStringCellValue();
                        /*在这里可以对每个单元格中的值进行二次操作转化*/
                        //将每行不同列的内容放入数组
                        values[c] = cellValue;

                    }
                    //将读取出来的内容写入mysql数据库
                    try {

                        pstatement = con.prepareStatement("insert "+tableName+" values("+str+");");
                        for (int i = 0; i < cellCount; i++) {
                            pstatement.setNString(i+1, values[i]);
                        }
                        pstatement.executeUpdate();  //执行sql语句插入内容
                        res=0;
                    } catch (SQLException e) {
                        // TODO Auto-generated catch block
                        e.printStackTrace();
                    }

                }
            }
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        return res;
    }

/*    public static void main(String[] args) {
        //readExcel(new File("E:\\testproject.xlsx"));
        String str = "testproject";

        readExcel(str,new File("E:\\testproject.xlsx"));
        System.out.println("成功");
    }*/
}
